# -*- coding: utf-8 -*-
'''
Created on 2018.01.24
@author: wuyou
'''

import pymysql
from sshtunnel import SSHTunnelForwarder
from MyLibrary.ReadWriteExcel import ReadWriteExcel
import time
excel = ReadWriteExcel()

config_excel = r'..\Config\Configuration.xlsx'
config_sheet = 'env_config'

class QuerySQL():

    def __init__(self):
        end = excel.get_excel_row_count(config_excel, config_sheet)
        configdict=excel.get_excel_name_value_dict(config_excel, config_sheet,'B','C','2',end)
        self.ssh_host = configdict['ssh_host']
        self.ssh_usr = configdict['ssh_usr']
        self.ssh_psw = configdict['ssh_psw']
        self.sql_addr = configdict['sql_addr']
        self.sql_usr = configdict['sql_usr']
        self.sql_psw = configdict['sql_psw']
        self.sql_port = configdict['sql_port']
        self.credit_portal_db = configdict['credit_portal_db']
        self.credit_backend_db = configdict['credit_backend_db']

    def connectssh(self,dbinfo):
        server = SSHTunnelForwarder(
				(self.ssh_host, 22),
				ssh_username = self.ssh_usr,
				ssh_password = self.ssh_psw,
				remote_bind_address=(self.sql_addr, int(self.sql_port)))
        server.start()
        return server

    def stopssh(self,server):
        server.stop()

    def connectdb(self,dbname):
        server = SSHTunnelForwarder(
				(self.ssh_host, 22),
				ssh_username = self.ssh_usr,
				ssh_password = self.ssh_psw,
				remote_bind_address=(self.sql_addr, int(self.sql_port)))
        server.start()
        serverport = server.local_bind_port
        con = pymysql.connect(host='127.0.0.1',
#							   port=server.local_bind_port,
							   port=serverport,
							   user=self.sql_usr,
							   passwd=self.sql_psw,
		                       db=dbname,
							   charset='utf8')
        return server,con,serverport

    def get_one_value(self,db,sqlcommand):
        cursor = db.cursor()
        print("SQL语句：   "+str(sqlcommand))
        result = ()
        for i in range(30):
            time.sleep(2)
            if len(result) == 0:
                try:
                    cursor.execute(sqlcommand)
                    result = cursor.fetchall()
                    print(result)
                except Exception as error:
                    print(error)
            else:
                print("Check Sucessful ! ")
                break
        if len(result) != 0:
            result = str(result[0][0])
        else:
            result= 'empty'
        return result

    def run_sql_single_with_value(self, db, sqlcommand, value):
        cursor = db.cursor()
        test_sql = str(sqlcommand)
        print("SQL语句：   "+test_sql)
        value = [value]
        result = ()
        for i in range(30):
            time.sleep(2)
            if len(result)==0:
                try:
                    cursor.execute(test_sql, value)
                    result = cursor.fetchall()
                    print(result)
                except Exception as error:
                    print(error)
            else:
                print("Check Sucessful ! ")
                break
        if len(result)!=0:
            result = str(result[0][0])
        else:
            result = "empty"
        return result


#sql查询，得到一个横行，取值，返回list


    def run_sql_single_with_value_return_list(self, db, sqlcommand, value):
        cursor = db.cursor()
        test_sql = str(sqlcommand)
        print("SQL语句：   "+test_sql)
        value = [value]

        resultlist = []
        result = ()
        for i in range(30):
            time.sleep(2)
            if len(result) == 0:
                try:
                    cursor.execute(test_sql, value)
                    result = cursor.fetchall()
                    print(result)
                except Exception as error:
                    print(error)
            else:
                print("Check Sucessful ! ")
                break
        if len(result) != 0:
            for i in range(len(result[0])):
                resultlist.append(str(result[0][i]))
        else:
            result = "empty"
            resultlist.append(result)
        return resultlist

    #sql查询，得到一个竖列，取值，返回list
    def run_sql_single_with_value_return_list2(self, db, sqlcommand, value):
        cursor = db.cursor()
        test_sql = str(sqlcommand)
        print("SQL语句：   "+test_sql)
        value = [value]

        resultlist = []
        result = ()
        for i in range(15):
            time.sleep(1)
            if len(result)==0:
                try:
                    cursor.execute(test_sql, value)
                    result = cursor.fetchall()
#                    result = cursor.fetchmany(50)
                except Exception as error:
                    print(error)
            else:
                try:
                    cursor.execute(test_sql, value)
                    time.sleep(1)
                    result = cursor.fetchall()
                    cursor.execute(test_sql, value)
                    time.sleep(1)
                    result = cursor.fetchall()
#                    result = cursor.fetchmany(50)
                    print(result)
                except Exception as error:
                    print(error)

                print("Check Sucessful ! ")
                break
        if len(result)!=0:
            for i in range(len(result)):
                resultlist.append(str(result[i][0]))
        else:
            result = "empty"
            resultlist.append(result)
        return resultlist

if __name__ == '__main__':
	run = QuerySQL()
	dbinfo = {'ssh_host':'47.94.40.127',\
	          'ssh_usr':'appuser',\
	          'ssh_psw':'5zt4BRJ8',\
	          'sql_addr':'rdsiuzzzqiuzzzq.mysql.rds.aliyuncs.com',\
	          'sql_port':3306,\
	          'sql_usr':'sit_user01',\
	          'sql_psw':'j*IHNifVbxCJ',\
	          'dbname':'test_base_db'}
	sqlcommand = 'SELECT rule_code FROM t_workflow_rule a INNER JOIN t_workflow_apply b WHERE  a.excute_id = b.excute_id AND b.flow_id=%s'
	con_backend_list = run.connectdb(run.credit_backend_db)
	server_backend = con_backend_list[0]
	con_backend = con_backend_list[1]

	result = run.run_sql_single_with_value_return_list2(con_backend, sqlcommand, "0514b-dc0af030d5b0")
	print(result)

	server_backend.stop()
	con_backend.close()
